IF EXISTS (SELECT * FROM sysobjects WHERE name = 'SaveInstanceInfoFromWMI' AND type = 'P')
     DROP PROCEDURE dbo.SaveInstanceInfoFromWMI
GO

CREATE PROCEDURE dbo.SaveInstanceInfoFromWMI
     @PackageExecutionId bigint,
     @NoGuessingFlag     tinyint = 0
AS
/*
 *
 * SaveInstanceInfoFromWMI
 *
 * Purpose:      
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      
 *
 * Note(s):      
 *
 * CfgCtrl Keys: None
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 10/19/2011, 1.00.0000
 *               NCL, 11/17/2011, 1.00.0001
 *               Modified for installed services handling.
 *               NCL, 12/08/2011, 1.00.0002
 *               Bug fix, "deleted" services of machines OTHER than the one
 *               it was currently working on.
 *
 */
BEGIN
     SET NOCOUNT ON
     SET XACT_ABORT ON

     DECLARE @RecId                int,
             @SrvrInstncId         int,
             @ActualInstanceName   varchar(255),
             @tempFullInstanceName varchar(255)

     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- 
     -- Handle NULL Actual Names
     -- 
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------

     -- These are instances that we were unable to connect to to determine the 
     -- machine name that it reports.  This could be because although we detected
     -- the service it was not running or perhaps it was identified on a cluster
     -- node and we could not connect to it by the name specified.  But since 
     -- we can make some assumptions we can perhaps guess, such as for a given 
     -- machine a named instance can only be used once either stand-alone or 
     -- clustered.  Hopefully these variations will be handled during the 
     -- machine inserts during cluster node determination in the instance
     -- processing.
     IF ( @NoGuessingFlag <> 1 )
     BEGIN
          UPDATE tempdb.dbo.tempDiscoverMSSQLServersList
          SET    SrvrInstncId = sim.SrvrInstncId
          FROM   dbo.MachineCluster mc INNER JOIN dbo.Machine m
                    ON mc.MemberMachineId = m.MachineId
                 INNER JOIN tempdb.dbo.tempDiscoverMSSQLServersList dsl
                    ON m.MachineName = dsl.MachineName
                 INNER JOIN dbo.SrvrInstncMachine sim
                    ON sim.MachineId = m.MachineId
                 INNER JOIN dbo.SrvrInstncMSSQL sims
                    ON sims.SrvrInstncId           = sim.SrvrInstncId
                   AND sims.SrvrInstncInstanceName = dsl.InstanceName
          WHERE  dsl.ActualFullInstanceName IS NULL
     END

     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- 
     -- Handle Non-NULL Actual Names
     -- 
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------

     -- We will now try and lookup any existing server instance ids, the ones
     -- where we find a record will be treated as aliases to the server instance
     -- where we don't will be added as new instances.

     UPDATE tempdb.dbo.tempDiscoverMSSQLServersList
     SET    SrvrInstncId = si.SrvrInstncId
     FROM   tempdb.dbo.tempDiscoverMSSQLServersList dsl INNER JOIN dbo.SrvrInstnc si
               ON dsl.ActualFullInstanceName = si.SrvrInstncFullName
     WHERE  dsl.ActualFullInstanceName IS NOT NULL

     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- 
     -- Perform consolidated processing
     -- 
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------

     -- We'll start by adding new server instances
     DECLARE NewInstance CURSOR FOR SELECT RecId
                                    FROM   tempdb.dbo.tempDiscoverMSSQLServersList
                                    WHERE  ActualFullInstanceName IS NOT NULL
                                    AND    SrvrInstncId           IS NULL

     OPEN NewInstance

     FETCH NEXT FROM NewInstance INTO @RecId

     WHILE ( @@FETCH_STATUS = 0 )
     BEGIN
          SELECT @ActualInstanceName   = ActualFullInstanceName,
                 @tempFullInstanceName = FullInstanceName
          FROM   tempdb.dbo.tempDiscoverMSSQLServersList
          WHERE  RecId = @RecId

          -- Just in case something weird happens we are gonna check to be sure
          -- that the actual name hasn't already been added, there is a case
          -- were it might be possible to have more than one record line up but
          -- it is an edge case that should never occur but we'll check anyway.
          IF EXISTS ( SELECT 1 FROM dbo.SrvrInstnc WHERE SrvrInstncFullName = @ActualInstanceName )
          BEGIN -- We found it so we get the value for SrvrInstncId
               SELECT @SrvrInstncId = SrvrInstncId 
               FROM   dbo.SrvrInstnc 
               WHERE  SrvrInstncFullName = @ActualInstanceName
          END
          ELSE
          BEGIN
               EXEC dbo.InsSrvrInstnc
                         @SrvrInstncFullName = @ActualInstanceName,
                         @SrvrInstncTypeId   = 1, -- MSSQL
                         @SrvrInstncGrpId    = 1, -- Default Group
                         @SrvrInstncId       = @SrvrInstncId OUTPUT
          END

          IF ( @ActualInstanceName != @tempFullInstanceName ) 
          BEGIN -- We have a case where the actual name and reported name differ
                -- we add an alias
               INSERT dbo.SrvrInstncAlias
                    ( SrvrInstncId,
                      SrvrInstncFullName )
               SELECT @SrvrInstncId,
                      @tempFullInstanceName
          END

          FETCH NEXT FROM NewInstance INTO @RecId
     END

     CLOSE NewInstance
     DEALLOCATE NewInstance

     -- We will handle the cases where have found a SrvrInstnc Id, these should 
     -- be aliases but we will check, we insert aliases directly.
     INSERT dbo.SrvrInstncAlias
          ( SrvrInstncId,
            SrvrInstncFullName,
            DoNotUseFlag,
            LastModDt )
     SELECT SrvrInstncId,
            FullInstanceName,
            DoNotUseFlag = CASE WHEN dsl.ActualFullInstanceName IS NULL THEN 1 ELSE 0 END,
            CURRENT_TIMESTAMP
     FROM   tempdb.dbo.tempDiscoverMSSQLServersList dsl
     -- Be sure it don't exist in the server instance already
     WHERE  NOT EXISTS ( SELECT 1
                         FROM   dbo.SrvrInstnc si
                         WHERE  si.SrvrInstncFullName = dsl.FullInstanceName )
     -- Be sure it isn't already an alias
     AND    NOT EXISTS ( SELECT 1
                         FROM   dbo.SrvrInstncAlias sia
                         WHERE  sia.SrvrInstncFullName = dsl.FullInstanceName )
     AND    dsl.SrvrInstncId IS NOT NULL

     -- Last we will handle the records that are unresolved, we will store them
     -- for manual resolution at a later time.
     INSERT dbo.UnresolvedMSSQLInstnc
          ( PackageExecutionId,
            FullInstanceName,
            MachineName,
            InstanceName,
            LoginName,
            ServiceRunningFlag,
            IsClusteredFlag,
            ErrorDesc )
     SELECT PackageExecutionId,
            FullInstanceName,
            MachineName,
            InstanceName,
            LoginName,
            ServiceRunningFlag,
            IsClusteredFlag,
            ErrorDesc
     FROM   tempdb.dbo.tempDiscoverMSSQLServersList dsl
     WHERE  NOT EXISTS ( SELECT 1
                         FROM   dbo.UnresolvedMSSQLInstnc u
                         WHERE  u.FullInstanceName = dsl.FullInstanceName )
     AND    dsl.ActualFullInstanceName IS NULL
     AND    dsl.SrvrInstncId           IS NULL

     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- 
     -- Perform MS Installed Service Processing.
     -- 
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------

     -- Condition IsClusteredFlag
     UPDATE tempdb.dbo.tempDiscoverMSInstalledServersList
     SET    IsClusteredFlag = CASE WHEN IsClusteredFlag = 1 THEN 1 ELSE 0 END
     FROM   tempdb.dbo.tempDiscoverMSInstalledServersList
     WHERE  IsClusteredFlag IS NULL
     OR     IsClusteredFlag NOT IN ( 0, 1 )

     -- Get Machine Ids for machines.
     UPDATE tempdb.dbo.tempDiscoverMSInstalledServersList
     SET    MachineId = m.MachineId
     FROM   tempdb.dbo.tempDiscoverMSInstalledServersList sl INNER JOIN dbo.Machine m
               ON sl.MachineName = m.MachineName

     -- Get Server Instance Id for SQL Servers
     UPDATE tempdb.dbo.tempDiscoverMSInstalledServersList
     SET    SrvrInstncId = si.SrvrInstncId
     FROM   tempdb.dbo.tempDiscoverMSInstalledServersList sl INNER JOIN dbo.SrvrInstnc si
               ON sl.FullInstanceName = si.SrvrInstncFullName
     WHERE  si.SrvrInstncTypeId = 1 -- MSSQL
     AND    sl.ServiceTypeId    = 1 -- SQL Server

     -- Get Server Instance Id for SQL Servers By Alias
     UPDATE tempdb.dbo.tempDiscoverMSInstalledServersList
     SET    SrvrInstncId = sia.SrvrInstncId
     FROM   tempdb.dbo.tempDiscoverMSInstalledServersList sl INNER JOIN dbo.SrvrInstncAlias sia
               ON sl.FullInstanceName = sia.SrvrInstncFullName
            INNER JOIN dbo.SrvrInstnc si
               ON sia.SrvrInstncId = si.SrvrInstncId
     WHERE  si.SrvrInstncTypeId = 1 -- MSSQL
     AND    sl.ServiceTypeId    = 1 -- SQL Server
     AND    sia.DoNotUseFlag    = 0

     -- Play Paranoid for a minute, we make sure that the correlations have been
     -- made for MSSQL Servers.
     INSERT dbo.SrvrInstncMSSQL
          ( SrvrInstncId,
            SrvrInstncMachineName,
            SrvrInstncInstanceName )
     SELECT si.SrvrInstncId,
            dbo.GetMachineNameFromMSSQLFullInstanceName( si.SrvrInstncFullName ),
            dbo.GetInstanceNameFromMSSQLFullInstanceName( si.SrvrInstncFullName )
     FROM   dbo.SrvrInstnc si
     WHERE  SrvrInstncTypeId = 1 -- MSSQL
     AND    NOT EXISTS ( SELECT 1
                         FROM   dbo.SrvrInstncMSSQL sim
                         WHERE  sim.SrvrInstncId = si.SrvrInstncId )

     -- Get Server Instance Id for SQL Servers when we are referring to the base
     -- machine name with or without an instance and not the virtual name.  This
     -- only happens for clustered machines.
     UPDATE tempdb.dbo.tempDiscoverMSInstalledServersList
     SET    SrvrInstncId = si.SrvrInstncId
     FROM   tempdb.dbo.tempDiscoverMSInstalledServersList sl INNER JOIN dbo.SrvrInstncMachine sim
               ON sl.MachineId = sim.MachineId 
            INNER JOIN dbo.SrvrInstncMSSQL simm
               ON sim.SrvrInstncId = simm.SrvrInstncId
              AND simm.SrvrInstncInstanceName = sl.InstanceName
            INNER JOIN dbo.SrvrInstnc si
               ON sim.SrvrInstncId = si.SrvrInstncId
     WHERE  si.SrvrInstncTypeId = 1 -- MSSQL
     AND    sl.ServiceTypeId    = 1 -- SQL Server
     AND    sl.IsClusteredFlag  = 1

     -- Handled related services
     UPDATE sl2
     SET    RelatedToSrvrInstncId = sl1.SrvrInstncId
     FROM   tempdb.dbo.tempDiscoverMSInstalledServersList sl1 INNER JOIN tempdb.dbo.tempDiscoverMSInstalledServersList sl2
               ON sl1.MachineId     = sl2.MachineId
              AND sl1.InstanceName  = sl2.InstanceName
              AND sl1.ServiceTypeId = 1 
              AND sl2.ServiceTypeId IN ( 2, 5, 9 ) -- Agent, SSAS, Full-Text

     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- 
     -- Write MS Installed Services
     -- 
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------

     -- Handle Insert
     INSERT dbo.MachineMSInstalledComponents
          ( MachineId,
            PackageExecutionId,
            ServiceName,
            InstanceName,
            InstalledComponentName,
            IsClusteredFlag,
            SrvrInstncId,
            InstalledComponentDeletedFlag,
            LastModDt )
     SELECT sl.MachineId,
            sl.PackageExecutionId,
            sl.ServiceName,
            sl.InstanceName,
            sl.ServiceType,
            sl.IsClusteredFlag,
            sl.SrvrInstncId,
            0, -- InstalledComponentDeletedFlag
            CURRENT_TIMESTAMP -- LastModDt
     FROM   tempdb.dbo.tempDiscoverMSInstalledServersList sl
     WHERE  NOT EXISTS ( SELECT 1
                         FROM   dbo.MachineMSInstalledComponents m
                         WHERE  sl.MachineId   = m.MachineId 
                         AND    sl.ServiceName = m.ServiceName )
     AND    sl.MachineId IS NOT NULL 

     -- Handle Deletes
     UPDATE dbo.MachineMSInstalledComponents
     SET    InstalledComponentDeletedFlag = 1
     FROM   dbo.MachineMSInstalledComponents m
     WHERE  NOT EXISTS ( SELECT 1
                         FROM   tempdb.dbo.tempDiscoverMSInstalledServersList sl
                         WHERE  sl.MachineId   = m.MachineId 
                         AND    sl.ServiceName = m.ServiceName )
     -- Only "delete" those services on the current machine.
     AND    EXISTS ( SELECT 1
                     FROM   tempdb.dbo.tempDiscoverMSInstalledServersList sl
                     WHERE  sl.MachineId   = m.MachineId )

     -- Handle Re-Installed/Updated Services, MachineId, InstanceName and 
     -- ServiceName cannot be changed, PEI and LastModDt are not checked
     UPDATE dbo.MachineMSInstalledComponents
     SET    PackageExecutionId            = sl.PackageExecutionId,
            InstalledComponentName        = sl.ServiceType,
            IsClusteredFlag               = sl.IsClusteredFlag,
            SrvrInstncId                  = sl.SrvrInstncId,
            RelatedToSrvrInstncId         = sl.RelatedToSrvrInstncId,
            InstalledComponentDeletedFlag = 0,
            LastModDt                     = CURRENT_TIMESTAMP
     FROM   tempdb.dbo.tempDiscoverMSInstalledServersList sl INNER JOIN dbo.MachineMSInstalledComponents m
               ON sl.MachineId   = m.MachineId 
              AND sl.ServiceName = m.ServiceName
     WHERE  m.InstalledComponentDeletedFlag = 1
     OR   ( m.InstalledComponentDeletedFlag = 0
        AND ( m.InstalledComponentName != sl.ServiceType
           OR m.IsClusteredFlag        != sl.IsClusteredFlag
           OR ( m.SrvrInstncId                       != sl.SrvrInstncId
           OR ( m.SrvrInstncId          IS NOT NULL AND sl.SrvrInstncId          IS     NULL
             OR m.SrvrInstncId          IS     NULL AND sl.SrvrInstncId          IS NOT NULL )
              )
           OR ( m.RelatedToSrvrInstncId              != sl.RelatedToSrvrInstncId
           OR ( m.RelatedToSrvrInstncId IS NOT NULL AND sl.RelatedToSrvrInstncId IS     NULL
             OR m.RelatedToSrvrInstncId IS     NULL AND sl.RelatedToSrvrInstncId IS NOT NULL )
             )
            )
          )

     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- 
     -- End
     -- 
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
     -- ------------------------------------------------------------------------
END
GO

EXEC sp_addextendedproperty @name = N'MS_Description', @value = 'We take the list and try to make relationships to the existing server instances, it will also add server instances where we can clearly identify them.', @level0type = N'Schema', @level0name = dbo, @level1type = N'PROCEDURE', @level1name = SaveInstanceInfoFromWMI;
GO
